package com.workingpub.commons.jdbc.internal;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.util.Assert;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import com.workingpub.commons.jdbc.JdbcManager;
import com.workingpub.commons.utils.Page;

public abstract class AbstractJdbcManager implements JdbcManager {
	protected final Logger logger = LoggerFactory.getLogger(AbstractJdbcManager.class);

	protected NamedParameterJdbcTemplate mainJdbcTemplate;

	@Override
	public <T extends Object> Page<T> queryForPage(String querySql, RowMapper<T> rowMapper, int pageNo, int pageSize) {
		return queryForPage(querySql, QUERY_DEFAULT_SINGLE_TABLE, rowMapper, pageNo, pageSize);
	}

	@Override
	public <T extends Object> Page<T> queryForPage(String querySql, Map<String, Object> queryParam,
			RowMapper<T> rowMapper, int pageNo, int pageSize) {
		return queryForPage(querySql, QUERY_DEFAULT_SINGLE_TABLE, queryParam, rowMapper, pageNo, pageSize);
	}

	@Override
	public <T extends Object> Page<T> queryForPage(String querySql, List<Object> queryParam, RowMapper<T> rowMapper,
			int pageNo, int pageSize) {
		return queryForPage(querySql, QUERY_DEFAULT_SINGLE_TABLE, queryParam, rowMapper, pageNo, pageSize);
	}

	@Override
	public <T extends Object> Page<T> queryForPage(String querySql, String countSql, RowMapper<T> rowMapper, int pageNo,
			int pageSize) {
		Assert.notNull(querySql, "query sql is must be not null");
		Assert.notNull(rowMapper, "call-back interface RowMapper is must by not null");

		int totalCount = this.queryCont(querySql, countSql);
		if (totalCount == 0) {
			return new Page<T>(Page.DEFAULT_PAGE_NUMBER, 0, pageSize, new ArrayList<T>());
		}

		Map<String, Object> queryParam = new HashMap<String, Object>();
		String pageSql = buildQueryPageSql(querySql, queryParam, Page.getStartOfPage(pageNo, pageSize), pageSize);
		if (!StringUtils.hasText(pageSql)) {
			return new Page<T>(Page.DEFAULT_PAGE_NUMBER, 0, pageSize, new ArrayList<T>());
		} else {
			List<T> listData = this.mainJdbcTemplate.query(pageSql, queryParam, rowMapper);
			if (CollectionUtils.isEmpty(listData)) {
				return new Page<T>(Page.DEFAULT_PAGE_NUMBER, 0, pageSize, new ArrayList<T>());
			} else {
				return new Page<T>(pageNo, totalCount, pageSize, listData);
			}
		}
	}

	@Override
	public <T extends Object> Page<T> queryForPage(String querySql, String countSql, Map<String, Object> queryParam,
			RowMapper<T> rowMapper, int pageNo, int pageSize) {
		Assert.notNull("query sql is must be not null", querySql);
		Assert.notNull(rowMapper, "call-back interface RowMapper is must by not null");

		if (CollectionUtils.isEmpty(queryParam)) {
			return queryForPage(querySql, countSql, rowMapper, pageNo, pageSize);
		}

		int totalCount = this.queryCont(querySql, countSql, queryParam);
		if (totalCount == 0) {
			return new Page<T>(Page.DEFAULT_PAGE_NUMBER, 0, pageSize, new ArrayList<T>());
		}

		String pageSql = buildQueryPageSql(querySql, queryParam, Page.getStartOfPage(pageNo, pageSize), pageSize);
		if (!StringUtils.hasText(pageSql)) {
			return new Page<T>(Page.DEFAULT_PAGE_NUMBER, 0, pageSize, new ArrayList<T>());
		} else {
			List<T> listData = this.mainJdbcTemplate.query(pageSql, queryParam, rowMapper);
			if (CollectionUtils.isEmpty(listData)) {
				return new Page<T>(Page.DEFAULT_PAGE_NUMBER, 0, pageSize, new ArrayList<T>());
			} else {
				return new Page<T>(pageNo, totalCount, pageSize, listData);
			}
		}
	}

	@Override
	public <T extends Object> Page<T> queryForPage(String querySql, String countSql, List<Object> queryParam,
			RowMapper<T> rowMapper, int pageNo, int pageSize) {
		Assert.notNull(querySql, "query sql is must be not null");
		Assert.notNull(rowMapper, "call-back interface RowMapper is must by not null");

		if (CollectionUtils.isEmpty(queryParam)) {
			return queryForPage(querySql, countSql, rowMapper, pageNo, pageSize);
		}

		int totalCount = this.queryCont(querySql, countSql, queryParam);
		if (totalCount == 0) {
			return new Page<T>(Page.DEFAULT_PAGE_NUMBER, 0, pageSize, new ArrayList<T>());
		}

		String pageSql = buildQueryPageSql(querySql, queryParam, Page.getStartOfPage(pageNo, pageSize), pageSize);
		if (!StringUtils.hasText(pageSql)) {
			return new Page<T>(Page.DEFAULT_PAGE_NUMBER, 0, pageSize, new ArrayList<T>());
		} else {
			List<T> listData = this.mainJdbcTemplate.getJdbcOperations().query(pageSql, rowMapper,
					queryParam.toArray());
			if (CollectionUtils.isEmpty(listData)) {
				return new Page<T>(Page.DEFAULT_PAGE_NUMBER, 0, pageSize, new ArrayList<T>());
			} else {
				return new Page<T>(pageNo, totalCount, pageSize, listData);
			}
		}
	}

	@Override
	public <T extends Object> List<T> queryForList(String querySql, RowMapper<T> rowMapper) {
		Assert.notNull(querySql, "query sql is must be not null");
		Assert.notNull(rowMapper, "call-back interface RowMapper is must by not null");
		logger.debug("\n" + querySql);
		return this.mainJdbcTemplate.getJdbcOperations().query(querySql, rowMapper, new Object[] {});
	}

	@Override
	public <T extends Object> List<T> queryForList(String querySql, Map<String, Object> queryParam,
			RowMapper<T> rowMapper) {
		Assert.notNull(querySql, "query sql is must be not null");
		Assert.notNull(rowMapper, "call-back interface RowMapper is must by not null");
		if (CollectionUtils.isEmpty(queryParam)) {
			return queryForList(querySql, rowMapper);
		}
		logger.debug("Query parameters : " + queryParam);
		return this.mainJdbcTemplate.query(querySql, queryParam, rowMapper);
	}

	@Override
	public <T extends Object> List<T> queryForList(String querySql, List<Object> queryParam, RowMapper<T> rowMapper) {
		return queryForList(querySql, rowMapper, queryParam.toArray());
	}

	@Override
	public <T extends Object> List<T> queryForList(String querySql, RowMapper<T> rowMapper, Object... queryParam) {
		Assert.notNull(querySql, "query sql is must be not null");
		Assert.notNull(rowMapper, "call-back interface RowMapper is must by not null");
		logger.debug("\n" + querySql);
		logger.debug("\n" + Arrays.toString(queryParam));
		if (null == queryParam || queryParam.length == 0) {
			return queryForList(querySql, rowMapper);
		} else {
			logger.debug("Query parameters : " + Arrays.toString(queryParam));
			return this.mainJdbcTemplate.getJdbcOperations().query(querySql, rowMapper, queryParam);
		}
	}

	@Override
	public <T extends Object> T queryForObject(String querySql, RowMapper<T> rowMapper) {
		Assert.notNull(querySql, "query sql is must be not null");
		Assert.notNull(rowMapper, "call-back interface RowMapper is must by not null");
		logger.debug("\n" + querySql);

		try {
			return this.mainJdbcTemplate.getJdbcOperations().queryForObject(querySql, rowMapper, new Object[] {});
		} catch (EmptyResultDataAccessException ex) {
			logger.warn(ex.getMessage());
			return null;
		}
	}

	@Override
	public <T extends Object> T queryForObject(String querySql, Map<String, Object> queryParam,
			RowMapper<T> rowMapper) {
		Assert.notNull(querySql, "query sql is must be not null");
		Assert.notNull(rowMapper, "call-back interface RowMapper is must by not null");
		if (CollectionUtils.isEmpty(queryParam)) {
			queryForObject(querySql, rowMapper);
		}
		logger.debug("Query parameters : " + queryParam);
		try {
			return this.mainJdbcTemplate.queryForObject(querySql, queryParam, rowMapper);
		} catch (EmptyResultDataAccessException ex) {
			logger.warn(ex.getMessage());
			return null;
		}
	}

	@Override
	public <T extends Object> T queryForObject(String querySql, List<Object> queryParam, RowMapper<T> rowMapper) {
		return queryForObject(rowMapper, querySql, queryParam.toArray());
	}

	@Override
	public <T extends Object> T queryForObject(RowMapper<T> rowMapper, String querySql, Object... queryParam) {
		Assert.notNull(querySql, "query sql is must be not null");
		Assert.notNull(rowMapper, "call-back interface RowMapper is must by not null");
		if (null == queryParam || queryParam.length == 0) {
			return queryForObject(querySql, rowMapper);
		}
		logger.debug("\n" + querySql);
		logger.debug("\n" + Arrays.toString(queryParam));
		return this.mainJdbcTemplate.getJdbcOperations().queryForObject(querySql, rowMapper, queryParam);
	}

	@Override
	public String queryForString(String querySql, Object... queryParam) {
		Assert.notNull(querySql, "query sql is must be not null");
		logger.debug("\n" + querySql);
		if (null == queryParam || queryParam.length == 0) {
			return this.mainJdbcTemplate.getJdbcOperations().queryForObject(querySql, String.class);
		} else {
			return this.mainJdbcTemplate.getJdbcOperations().queryForObject(querySql, String.class, queryParam);
		}
	}

	@Override
	public String queryForString(String querySql, Map<String, Object> queryParam) {
		Assert.notNull(querySql, "query sql is must be not null");
		logger.debug("\n" + querySql);
		if (CollectionUtils.isEmpty(queryParam)) {
			return this.mainJdbcTemplate.getJdbcOperations().queryForObject(querySql, String.class);
		} else {
			return this.mainJdbcTemplate.queryForObject(querySql, queryParam, String.class);
		}
	}

	@Override
	public Integer queryForInteger(String querySql, Object... queryParam) {
		Assert.notNull(querySql, "query sql is must be not null");
		logger.debug("\n" + querySql);
		logger.debug("\n" + Arrays.toString(queryParam));
		if (null == queryParam || queryParam.length == 0) {
			return this.mainJdbcTemplate.getJdbcOperations().queryForObject(querySql, Integer.class);
		} else {
			return this.mainJdbcTemplate.getJdbcOperations().queryForObject(querySql, queryParam, Integer.class);
		}
	}

	@Override
	public Integer queryForInteger(String querySql, Map<String, Object> queryParam) {
		Assert.notNull(querySql, "query sql is must be not null");
		logger.debug("\n" + querySql);
		logger.debug("\n" + queryParam);
		if (CollectionUtils.isEmpty(queryParam)) {
			return this.mainJdbcTemplate.getJdbcOperations().queryForObject(querySql, Integer.class);
		} else {
			return this.mainJdbcTemplate.queryForObject(querySql, queryParam, Integer.class);
		}
	}

	@Override
	public Long queryForLong(String querySql, Object... queryParam) {
		Assert.notNull(querySql, "query sql is must be not null");
		logger.debug("\n" + querySql);
		logger.debug("\n" + Arrays.toString(queryParam));
		if (null == queryParam || queryParam.length == 0) {
			return this.mainJdbcTemplate.getJdbcOperations().queryForObject(querySql, Long.class);
		} else {
			return this.mainJdbcTemplate.getJdbcOperations().queryForObject(querySql, queryParam, Long.class);
		}
	}

	@Override
	public Long queryForLong(String querySql, Map<String, Object> queryParam) {
		Assert.notNull(querySql, "query sql is must be not null");
		logger.debug("\n" + querySql);
		logger.debug("\n" + queryParam);
		if (CollectionUtils.isEmpty(queryParam)) {
			return this.mainJdbcTemplate.getJdbcOperations().queryForObject(querySql, Long.class);
		} else {
			return this.mainJdbcTemplate.queryForObject(querySql, queryParam, Long.class);
		}
	}

	@Override
	public Date queryForDate(String querySql, Object... queryParam) {
		Assert.notNull(querySql, "query sql is must be not null");
		logger.debug("\n" + querySql);
		logger.debug("\n" + Arrays.toString(queryParam));
		if (null == queryParam || queryParam.length == 0) {
			return this.mainJdbcTemplate.getJdbcOperations().queryForObject(querySql, Date.class);
		} else {
			return this.mainJdbcTemplate.getJdbcOperations().queryForObject(querySql, Date.class, queryParam);
		}
	}

	@Override
	public Date queryForDate(String querySql, Map<String, Object> queryParam) {
		Assert.notNull(querySql, "query sql is must be not null");
		logger.debug("\n" + querySql);
		logger.debug("\n" + queryParam);
		if (CollectionUtils.isEmpty(queryParam)) {
			return this.mainJdbcTemplate.getJdbcOperations().queryForObject(querySql, Date.class);
		} else {
			return this.mainJdbcTemplate.queryForObject(querySql, queryParam, Date.class);
		}
	}

	@Override
	public BigDecimal queryForBigDecimal(String querySql, Object... queryParam) {
		Assert.notNull(querySql, "query sql is must be not null");
		logger.debug("\n" + querySql);
		logger.debug("\n" + Arrays.toString(queryParam));
		if (null == queryParam || queryParam.length == 0) {
			return this.mainJdbcTemplate.getJdbcOperations().queryForObject(querySql, BigDecimal.class);
		} else {
			return this.mainJdbcTemplate.getJdbcOperations().queryForObject(querySql, BigDecimal.class, queryParam);
		}
	}

	@Override
	public BigDecimal queryForBigDecimal(String querySql, Map<String, Object> queryParam) {
		Assert.notNull(querySql, "query sql is must be not null");
		logger.debug("\n" + querySql);
		logger.debug("\n" + queryParam);
		if (CollectionUtils.isEmpty(queryParam)) {
			return this.mainJdbcTemplate.getJdbcOperations().queryForObject(querySql, BigDecimal.class);
		} else {
			return this.mainJdbcTemplate.queryForObject(querySql, queryParam, BigDecimal.class);
		}
	}

	@Override
	public int update(String sql, Map<String, ?> paramMap) throws DataAccessException {
		logger.debug("Update sql : \n" + sql);
		logger.debug("Paramters : \n" + paramMap);
		try {
			return this.mainJdbcTemplate.update(sql, paramMap);
		} catch (DataAccessException ex) {
			logger.error(ex.getMessage(), ex);
			throw ex;
		}
	}

	@Override
	public int update(String sql, Object... args) throws DataAccessException {
		logger.debug("Update sql : \n" + sql);
		logger.debug("Paramters : \n" + Arrays.toString(args));
		try {
			return this.mainJdbcTemplate.getJdbcOperations().update(sql, args);
		} catch (DataAccessException ex) {
			logger.error(ex.getMessage(), ex);
			throw ex;
		}
	}

	@Override
	public int update(String sql, Object[] args, int[] argTypes) throws DataAccessException {
		logger.debug("Update sql : \n" + sql);
		logger.debug("Paramters : \n" + Arrays.toString(args));
		try {
			return this.mainJdbcTemplate.getJdbcOperations().update(sql, args, argTypes);
		} catch (DataAccessException ex) {
			logger.error(ex.getMessage(), ex);
			throw ex;
		}
	}

	@Override
	public int[] batchUpdate(String sql, Map<String, ?>[] batchValues) {
		logger.debug("Update sql : \n" + sql);
		logger.debug("batchValues : \n" + batchValues.length);
		int[] result = null;
		try {
			result = this.mainJdbcTemplate.batchUpdate(sql, batchValues);
		} catch (Exception ex) {
			logger.error(ex.getMessage(), ex);
			Assert.isNull(ex, ex.getMessage());
		}
		return result;
	}

	protected String buildQueryCountSqlDefault(String querySql) {
		Assert.isTrue(StringUtils.hasText(querySql), "count query sql string is must be not null or empty");
		StringBuilder stringBuilder = new StringBuilder("select count(1) from (");
		stringBuilder.append(querySql);
		stringBuilder.append(") temptable");
		return stringBuilder.toString();
	}

	protected String buildQueryCountSql(String querySql) {
		Assert.isTrue(StringUtils.hasText(querySql), "count query sql string is must be not null or empty");
		String where = null;
		int start = querySql.toLowerCase().indexOf("from");
		if (start != -1) {
			where = querySql.substring(start);
		}
		StringBuilder stringBuilder = new StringBuilder("select count(1) ");
		stringBuilder.append(where);
		return stringBuilder.toString();
	}

	protected String buildQueryCountSql(String querySql, String countSql) {
		String queryCountSql = null;
		if (QUERY_SINGLE_TABLE_NO.equalsIgnoreCase(countSql)) {
			queryCountSql = buildQueryCountSqlDefault(querySql);
		} else if (QUERY_SINGLE_TABLE_YES.equalsIgnoreCase(countSql)) {
			queryCountSql = buildQueryCountSql(querySql);
		} else {
			queryCountSql = countSql;
		}
		logger.debug("CountSQL : \n" + queryCountSql);
		return queryCountSql;
	}

	abstract protected String buildQueryPageSql(String querySql, Map<String, Object> queryParam, int startIndex,
			int pageSize);

	abstract protected String buildQueryPageSql(String querySql, List<Object> queryParam, int startIndex, int pageSize);

	protected int queryCont(String querySql) {
		String queryContString = buildQueryCountSql(querySql);
		if (!StringUtils.hasText(queryContString)) {
			return 0;
		}
		Number number = this.mainJdbcTemplate.getJdbcOperations().queryForObject(queryContString, Integer.class);
		return (number != null ? number.intValue() : 0);
	}

	protected int queryCont(String querySql, String countSql) {
		String queryContString = buildQueryCountSql(querySql, countSql);
		if (!StringUtils.hasText(queryContString)) {
			return 0;
		}
		Number number = this.mainJdbcTemplate.getJdbcOperations().queryForObject(queryContString, Integer.class);
		return (number != null ? number.intValue() : 0);
	}

	protected int queryCont(String querySql, Map<String, Object> queryParam) {
		if (CollectionUtils.isEmpty(queryParam)) {
			return queryCont(querySql);
		}
		String queryContString = buildQueryCountSql(querySql);
		if (!StringUtils.hasText(queryContString)) {
			return 0;
		}

		Number number = this.mainJdbcTemplate.queryForObject(queryContString, queryParam, Integer.class);
		return (number != null ? number.intValue() : 0);
	}

	protected int queryCont(String querySql, String countSql, Map<String, Object> queryParam) {
		if (CollectionUtils.isEmpty(queryParam)) {
			return queryCont(querySql, countSql);
		}
		String queryContString = buildQueryCountSql(querySql, countSql);
		if (!StringUtils.hasText(queryContString)) {
			return 0;
		}
		Number number = this.mainJdbcTemplate.queryForObject(queryContString, queryParam, Integer.class);
		return (number != null ? number.intValue() : 0);
	}

	protected int queryCont(String querySql, List<Object> queryParam) {
		if (CollectionUtils.isEmpty(queryParam)) {
			return queryCont(querySql);
		}
		String queryContString = buildQueryCountSql(querySql);
		if (!StringUtils.hasText(queryContString)) {
			return 0;
		}
		Number number = this.mainJdbcTemplate.getJdbcOperations().queryForObject(queryContString, queryParam.toArray(),
				Integer.class);
		return (number != null ? number.intValue() : 0);
	}

	protected int queryCont(String querySql, String countSql, List<Object> queryParam) {
		if (CollectionUtils.isEmpty(queryParam)) {
			return queryCont(querySql, countSql);
		}
		String queryContString = buildQueryCountSql(querySql, countSql);
		if (!StringUtils.hasText(queryContString)) {
			return 0;
		}
		Number number = this.mainJdbcTemplate.getJdbcOperations().queryForObject(queryContString, queryParam.toArray(),
				Integer.class);
		return (number != null ? number.intValue() : 0);
	}

	public void setMainJdbcTemplate(NamedParameterJdbcTemplate mainJdbcTemplate) {
		this.mainJdbcTemplate = mainJdbcTemplate;
	}

}
